"""
Case Type   : 基础功能-SQL语法-DDL-partition
Case Name   : Hash分区表执行move partition，并行跨分区merge into
Create At   : 2022/12/12
Owner       : kyeleze
Description :
    1 创建hash分区表，并插入数据
    2 第一个session开始事务A，move partition
    3 第二个session开始事务B，跨分区merge into
    4 每个session分别执行commit
    5 查询数据
Expect      :
    1.成功
    2.成功
    3.成功
    4.成功
    5.查询数据插入成功
History     :
"""
import os
import unittest

import py_opengauss
from yat.test import Node

from testcase.utils.Logger import Logger
from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant


class HashPartitionCase(unittest.TestCase):
    def setUp(self):
        self.log = Logger()
        self.log.info(f"-----{os.path.basename(__file__)} start-----")
        self.user_node = Node('PrimaryDbUser')
        self.primary_sh = CommonSH('PrimaryDbUser')
        self.constant = Constant()
        self.table_name1 = 't_hash_table_0099_1'
        self.table_name2 = 't_hash_table_0099_2'
        self.table_space = 't_tablespace_0099'
        self.user_param = f'-U {self.user_node.db_user} ' \
                          f'-W {self.user_node.db_password}'
        self.con_info = f'opengauss://' \
                        f'{self.user_node.db_user}:' \
                        f'{self.user_node.db_password}@' \
                        f'{self.user_node.db_host}:' \
                        f'{self.user_node.db_port}/' \
                        f'{self.user_node.db_name}'

    def test_partition_ddl_dml(self):
        step = '--step1:创建hash分区表，并插入数据 expect:成功--'
        self.log.info(step)
        sql_cmd = f'''drop table if exists {self.table_name1};create table 
        {self.table_name1} (col1 int, col2 int, col3 int) 
        partition by hash(col1) 
        (
          partition p1,
          partition p2
        );
        insert into {self.table_name1} values(1,1,1),(3,3,3);
        create tablespace {self.table_space} relative location
        'tablespace/tablespace_1';
        '''
        result = self.primary_sh.execut_db_sql(sql_cmd,
                                               sql_type=self.user_param)
        self.log.info(result)
        self.assertIn(self.constant.CREATE_TABLE_SUCCESS,
                      result, "执行失败:" + step)
        self.assertIn(self.constant.INSERT_SUCCESS_MSG,
                      result, "执行失败:" + step)
        sql_cmd = f'drop table if exists {self.table_name2};' \
                  f'create table {self.table_name2} ' \
                  f'(col1 int, col2 int, col3 int);' \
                  f'insert into {self.table_name2} values(1,1,2);'
        result = self.primary_sh.execut_db_sql(sql_cmd,
                                               sql_type=self.user_param)
        self.assertIn(self.constant.CREATE_TABLE_SUCCESS,
                      result, "执行失败:" + step)
        self.assertIn(self.constant.INSERT_SUCCESS_MSG,
                      result, "执行失败:" + step)

        step = '--step2:第一个session开始事务A，move partition expect:成功--'
        self.log.info(step)
        self.db1 = py_opengauss.open(self.con_info)
        begin_result = self.db1.query('begin;')
        self.log.info(begin_result)
        ddl_result = self.db1.query(f'alter table {self.table_name1} '
                                    f'move partition p1 tablespace '
                                    f'{self.table_space};')
        self.log.info(ddl_result)
        self.assertIn('BEGIN', begin_result, "执行失败:" + step)
        self.assertIn(self.constant.ALTER_TABLE_MSG,
                      ddl_result, "执行失败:" + step)

        step = '--step3:第二个session开始事务B，跨分区merge into expect:成功--'
        self.log.info(step)
        self.db2 = py_opengauss.open(self.con_info)
        begin_result = self.db2.query('begin;')
        self.log.info(begin_result)
        dml_result = self.db2.query(f'merge into {self.table_name1} t using '
                                    f'{self.table_name2} t1 on (t.col1 = 1) '
                                    f'when matched then update set '
                                    f't.col3 = t1.col3 where t.col1 = 1 '
                                    f'when not matched then insert values '
                                    f'(t1.col1,t1.col2, t1.col3) '
                                    f'WHERE t1.col2 = 1;')
        self.log.info(dml_result)
        self.assertIn('BEGIN', begin_result, "执行失败:" + step)
        self.assertIn('MERGE', dml_result, "执行失败:" + step)

        step = '--step4:两个session分别执行commit expect:成功--'
        self.log.info(step)
        db1_commit_result = self.db1.query('commit;')
        self.log.info(db1_commit_result)
        db2_commit_result = self.db2.query('commit;')
        self.log.info(db2_commit_result)
        self.assertIn('COMMIT', db1_commit_result, "执行失败:" + step)
        self.assertIn('COMMIT', db2_commit_result, "执行失败:" + step)

        step = '--step5:查询数据 expect:查询数据插入成功--'
        self.log.info(step)
        sql_cmd = f'select * from {self.table_name1};'
        result = self.primary_sh.execut_db_sql(sql_cmd,
                                               sql_type=self.user_param)
        self.log.info(result)
        self.assertIn('    1 |    1 |    2', result, "执行失败:" + step)

    def tearDown(self):
        step = '--恢复环境，关闭session，删除测试表--'
        self.log.info(step)
        self.db1.close()
        self.db2.close()
        sql_cmd = f'drop table {self.table_name1}, {self.table_name2};'
        result1 = self.primary_sh.execut_db_sql(sql_cmd)
        self.log.info(result1)
        result2 = self.primary_sh.execut_db_sql(
            f'drop tablespace {self.table_space};')
        self.log.info(result2)
        self.assertIn(self.constant.DROP_TABLE_SUCCESS,
                      result1, "执行失败:" + step)
        self.assertIn(self.constant.TABLESPCE_DROP_SUCCESS,
                      result2, "执行失败:" + step)
        self.log.info(f'-----{os.path.basename(__file__)} end-----')
